package com.enhinck.dbutilweb.controller;

import com.enhinck.dbutilweb.common.WebResponse;
import com.enhinck.dbutilweb.common.database.ConnectionWrapper;
import com.enhinck.dbutilweb.common.database.DbPoolUtil;
import com.enhinck.dbutilweb.common.database.MysqlDBUtil;
import com.enhinck.dbutilweb.common.database.Table;
import com.enhinck.dbutilweb.common.entity.ExcelFile;
import com.enhinck.dbutilweb.common.entity.InformationSchemaColumns;
import com.enhinck.dbutilweb.common.entity.InformationSchemaTables;
import com.enhinck.dbutilweb.common.entity.OneVersionModifySummary;
import com.enhinck.dbutilweb.common.main.MysqlDbCompare;
import com.enhinck.dbutilweb.common.main.MysqlDbToDictionary;
import com.enhinck.dbutilweb.common.word.CommonWordWriteUtil;
import com.enhinck.dbutilweb.model.domain.DatasourceConfDO;
import com.enhinck.dbutilweb.service.DatasourceConfService;
import io.swagger.annotations.Api;
import io.swagger.annotations.ApiOperation;
import lombok.AllArgsConstructor;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang3.StringUtils;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.RestController;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.util.List;
import java.util.Map;

/**
 * 数据源比较管理
 *
 * @author enhinck Generate auto created
 * @date 2021-03-03 10:52:04
 */
@Slf4j
@Api(description = "数据源比较管理", tags = {"DatasourceCompareController"})
@RestController
@AllArgsConstructor
public class DatasourceCompareController {
    private final DbPoolUtil dbPoolUtil;
    private final MysqlDBUtil mysqlDBUtil;

    /**
     * 数据库表列表
     *
     * @return
     */
    @ApiOperation("数据库表列表")
    @GetMapping("/datasourceconf/tables")
    public WebResponse<List<InformationSchemaTables>> tablesSearch(@RequestParam("id") Long id, @RequestParam(value = "tableName", required = false) String tableName) {
        ConnectionWrapper connectionWrapper = null;
        try {
            connectionWrapper = dbPoolUtil.getConnection(id);
            List<InformationSchemaTables> tables;
            if (StringUtils.isBlank(tableName)) {
                tables = mysqlDBUtil.getTables(connectionWrapper);
            } else {
                tables = mysqlDBUtil.getTables(connectionWrapper, tableName);
            }
            return WebResponse.ok(tables);
        } catch (Exception e) {
            log.error("{}", e);
            return WebResponse.error("400", "数据库获取链接失败");
        } finally {
            dbPoolUtil.release(connectionWrapper);
        }
    }


    /**
     * 数据库表列表
     *
     * @return
     */
    @ApiOperation("数据库表中字段列表")
    @GetMapping("/datasourceconf/table/columns")
    public WebResponse<List<InformationSchemaColumns>> columns(@RequestParam("id") Long id, @RequestParam("tableName") String tableName) {
        ConnectionWrapper connectionWrapper = null;
        try {
            connectionWrapper = dbPoolUtil.getConnection(id);
            List<InformationSchemaColumns> columns = mysqlDBUtil.getColumnsByTableName(tableName, connectionWrapper);
            return WebResponse.ok(columns);
        } catch (Exception e) {
            log.error("{}", e);
            return WebResponse.error("400", "数据库获取链接失败");
        } finally {
            dbPoolUtil.release(connectionWrapper);
        }
    }

    /**
     * 数据库表列表
     *
     * @return
     */
    @ApiOperation("数据库表中字段列表")
    @GetMapping("/datasourceconf/table/datas")
    public WebResponse<List<Map<String, Object>>> datas(@RequestParam("id") Long id, @RequestParam("tableName") String tableName, @RequestParam(value = "limit", defaultValue = "50", required = false) Long limit) {
        ConnectionWrapper connectionWrapper = null;
        try {
            connectionWrapper = dbPoolUtil.getConnection(id);
            List<Map<String, Object>> datas = mysqlDBUtil.selectAllLimit(tableName, connectionWrapper, limit);
            return WebResponse.ok(datas);
        } catch (Exception e) {
            log.error("{}", e);
            return WebResponse.error("400", "数据库获取链接失败");
        } finally {
            dbPoolUtil.release(connectionWrapper);
        }
    }

    /**
     * 数据库表列表
     *
     * @return
     */
    @ApiOperation("比对两个库的表结构并响应表结构升级sql")
    @GetMapping("/datasourceconf/compare/tables")
    public WebResponse<String> compareTables(@RequestParam("oldId") Long oldId, @RequestParam("newId") Long newId) {
        ConnectionWrapper connectionWrapperOld = null;
        ConnectionWrapper connectionWrapperNew = null;
        try {
            connectionWrapperOld = dbPoolUtil.getConnection(oldId);
            connectionWrapperNew = dbPoolUtil.getConnection(newId);
            OneVersionModifySummary oneVersionModifySummary = new OneVersionModifySummary();
            // 增量表语句
            String tableCreates = MysqlDbCompare.compareTableNames(connectionWrapperOld.getConnection(), connectionWrapperNew.getConnection(), oneVersionModifySummary);
            // 表字段新增和修改
            String columnsAddModify = MysqlDbCompare.compareTableColumns(connectionWrapperOld.getConnection(), connectionWrapperNew.getConnection(), oneVersionModifySummary);
            StringBuilder builder = new StringBuilder();
            builder.append(tableCreates).append("\n").append(columnsAddModify);
            return WebResponse.ok(builder.toString());
        } catch (Exception e) {
            log.error("{}", e);
            return WebResponse.error("400", "数据库获取链接失败");
        } finally {
            dbPoolUtil.release(connectionWrapperOld);
            dbPoolUtil.release(connectionWrapperNew);
        }
    }


    /**
     * 数据库表列表
     *
     * @return
     */
    @ApiOperation("比对两个库的表结构并响应表结构升级sql")
    @GetMapping("/datasourceconf/compare/selectTables")
    public WebResponse<String> compareTablesSelect(@RequestParam("oldId") Long oldId, @RequestParam("newId") Long newId,@RequestParam("tableNames") String tableNames) {
        ConnectionWrapper connectionWrapperOld = null;
        ConnectionWrapper connectionWrapperNew = null;
        try {
            connectionWrapperOld = dbPoolUtil.getConnection(oldId);
            connectionWrapperNew = dbPoolUtil.getConnection(newId);
            OneVersionModifySummary oneVersionModifySummary = new OneVersionModifySummary();
            // 增量表语句
            String tableCreates = MysqlDbCompare.compareTableNames(connectionWrapperOld.getConnection(), connectionWrapperNew.getConnection(),tableNames, oneVersionModifySummary);
            // 表字段新增和修改
            String columnsAddModify = MysqlDbCompare.compareTableColumns(connectionWrapperOld.getConnection(), connectionWrapperNew.getConnection(),tableNames, oneVersionModifySummary);
            StringBuilder builder = new StringBuilder();
            builder.append(tableCreates).append("\n").append(columnsAddModify);
            return WebResponse.ok(builder.toString());
        } catch (Exception e) {
            log.error("{}", e);
            return WebResponse.error("400", "数据库获取链接失败");
        } finally {
            dbPoolUtil.release(connectionWrapperOld);
            dbPoolUtil.release(connectionWrapperNew);
        }
    }

    /**
     * 数据库表列表
     *
     * @return
     */
    @ApiOperation("比对两个库的某些表的数据并响应表数据同步sql")
    @GetMapping("/datasourceconf/compare/tables/datas")
    public WebResponse<String> compareTables(@RequestParam("oldId") Long oldId, @RequestParam("newId") Long newId, @RequestParam("tableNames") String tableNames) {
        ConnectionWrapper connectionWrapperOld = null;
        ConnectionWrapper connectionWrapperNew = null;
        try {
            connectionWrapperOld = dbPoolUtil.getConnection(oldId);
            connectionWrapperNew = dbPoolUtil.getConnection(newId);
            // 增量表语句
            // 表字段新增和修改
            String result = MysqlDbCompare.dataCompare(tableNames, connectionWrapperOld.getConnection(), connectionWrapperNew.getConnection());
            return WebResponse.ok(result);
        } catch (Exception e) {
            log.error("{}", e);
            return WebResponse.error("400", "数据库获取链接失败");
        } finally {
            dbPoolUtil.release(connectionWrapperOld);
            dbPoolUtil.release(connectionWrapperNew);
        }
    }

    /**
     * 数据库表列表
     *
     * @return
     */
    @ApiOperation("某库的数据字典（可选参数只导出某些表的数据字典）")
    @GetMapping("/datasourceconf/tables/dictionary")
    public void compareTables(@RequestParam("id") Long id, @RequestParam(value = "tableNames", required = false) String tableNames, HttpServletRequest request, HttpServletResponse response) {
        ConnectionWrapper connectionWrapper = null;
        try {
            connectionWrapper = dbPoolUtil.getConnection(id);
            ExcelFile excelFile;
            if (StringUtils.isBlank(tableNames)) {
                excelFile = MysqlDbToDictionary.databaseDicDoc(connectionWrapper.getConnection());
            } else {
                excelFile = MysqlDbToDictionary.databaseDicDoc(connectionWrapper.getConnection(), tableNames);
            }
            CommonWordWriteUtil.write(excelFile, request, response);
        } catch (Exception e) {
            log.error("{}", e);
        } finally {
            dbPoolUtil.release(connectionWrapper);
        }
    }

}